﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CocisModel;
using CocisDBUtility;


namespace CocisDAL
{
    public class StaffDAL
    {
        private const string sql = "select * from userInfo where userName=@Id and Pwd=@Pwd ";

        private const string PARM_EMPNAME = "@Id";
        private const string PARM_EMPPWD = "@Pwd";

        #region 获取所有员工信息
        /// <summary>
        /// 获取所有员工信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllUserInfo()
        {
            string sql = "select * from viewUserInfo order by userId desc ";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);
        }
        #endregion

       
        #region 根据员工姓名和密码登陆
        /// <summary>
        /// 根据员工姓名和密码登陆
        /// </summary>
        /// <param name="id"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public UserInfo GetUserByNamePwd(string id, string pwd)
        {
            UserInfo userInfo = null;
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter(PARM_EMPNAME, SqlDbType.VarChar,50),
                new SqlParameter(PARM_EMPPWD, SqlDbType.VarChar,50)
            };
            parms[0].Value = id;
            parms[1].Value = pwd;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.ConStr, CommandType.Text, sql, parms))
            {
                if (dr.Read())
                {
                    userInfo = new UserInfo();
                    userInfo.UserId = dr.GetInt32(0);
                    userInfo.UserName = dr.GetString(1);
                    userInfo.Pwd = dr.GetString(2);
                    userInfo.DeptID = dr.GetInt32(3);
                    userInfo.RoleID = dr.GetInt32(4);
                    userInfo.EnglishName = dr.GetString(5);
                    userInfo.Sex = dr.GetString(6);                   
                    userInfo.Region = dr.GetString(7);
                    userInfo.OfficeTel = dr.GetString(8);
                    userInfo.ExtensionTel = dr.GetString(9);
                    userInfo.Mobile = dr.GetString(10);
                    userInfo.Email = dr.GetString(11);
                   
                
                }
            }

            return userInfo;
        }

        #endregion

        #region 根据员工用户名获取姓名
        /// <summary>
        /// 根据员工用户名获取姓名
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetNameByLoginId(int id)
        {
            string name = string.Empty;
            string sql = "select userName from userInfo where userId='" + id + "'";
            SqlDataReader sdr = SQLHelper.ExecuteReader(SQLHelper.ConStr, CommandType.Text, sql, null);
            if (sdr.Read())
            {
                name = sdr[0].ToString();
            }
            return name;
        }

        #endregion

        #region 新增用户信息
        /// <summary>
        /// 新增用户信息
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="pwd"></param>
        /// <param name="deptId"></param>
        /// <param name="roleId"></param>
        /// <param name="eName"></param>
        /// <param name="sex"></param>
        /// <param name="workCity"></param>
        /// <param name="officeTel"></param>
        /// <param name="shortTel"></param>
        /// <param name="mobile"></param>
        /// <param name="email"></param>
        /// <param name="flag"></param>
        /// <returns></returns>
        public int InsertNewUserInfo(string userName, string pwd, int deptId, int roleId, string englishName, string sex, string region, string officeTel, string extensionTel, string mobile, string email)
        {
            string sql = @"INSERT INTO userInfo(userName,pwd,deptId,roleId,englishName,sex,region,officeTel,extensionTel,mobile,email)
                           VALUES(@userName,@pwd,@deptId,@roleId,@englishName,@sex,@region,@officeTel,@extensionTel,@mobile,@email)";
           
            SqlParameter[] param = {
                                       new SqlParameter("@userName",userName),
                                       new SqlParameter("@pwd",pwd),
                                       new SqlParameter("@deptId",deptId),
                                       new SqlParameter("@roleId",roleId),
                                       new SqlParameter("@englishName",englishName),
                                       new SqlParameter("@sex",sex),
                                       new SqlParameter("@region",region),
                                       new SqlParameter("@officeTel",officeTel),
                                       new SqlParameter("@extensionTel",extensionTel),
                                       new SqlParameter("@mobile",mobile),
                                       new SqlParameter("@email",email)
                                   };
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr,CommandType.Text,sql,param);

        }
        #endregion

        #region 查询员工信息
        /// <summary>
        /// 查询员工信息
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="cName"></param>
        /// <param name="shopId"></param>
        /// <param name="deptId"></param>
        /// <param name="flag"></param>
        /// <returns></returns>
        public DataSet GetUserInfo(string loginName, string cName, int roleId, int deptId, int parentStaffId)
        {
            string sql = "select * from viewEmployee where 1=1 ";
            if (loginName != "")
            {
                sql += " and loginName like '%" + loginName + "%' ";
            }
            if (cName != "")
            {
                sql += " and cName like '%" + cName + "%' ";
            }

            if (deptId != 0)
            {
                sql += "and deptId='" + deptId + "'";
            }

            if (roleId != 0)
            {
                sql += "and roleId='" + deptId + "'";
            }


            //if (flag != 100)
            //{
            //    sql += "and enableFlag='" + flag + "'";
            //}

            sql += "order by empId desc";
            return SQLHelper.SqlDataAdapter(SQLHelper.ConStr, CommandType.Text, sql, null);

        }

        #endregion

        #region 查询员工信息
        /// <summary>
        /// 查询员工信息
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="cName"></param>
        /// <param name="shopId"></param>
        /// <param name="deptId"></param>
        /// <param name="flag"></param>
        /// <returns></returns>
        public List<UserInfo> GetUserInfoToTreeView(int deptId, int parentStaffId)
        {
            string sql = "select * from viewUserInfo where 1=1 ";
          

            if (deptId != 0)
            {
                sql += "and deptId='" + deptId + "'";
            }      


            sql += "order by userId desc";
            using (SqlDataReader read = SQLHelper.ExecuteReader(SQLHelper.ConStr, System.Data.CommandType.Text, sql, null))
            {
                List<UserInfo> list = new List<UserInfo>();
                while (read.Read())
                {
                    UserInfo userInfo = new UserInfo();

                    userInfo.UserId = read.GetInt32(0);
                    userInfo.UserName = read.GetString(1);
                    userInfo.Pwd = read.GetString(2);
                    userInfo.DeptID = read.GetInt32(3);
                    userInfo.RoleID = read.GetInt32(4);
                    userInfo.EnglishName = read.GetString(5);
                    userInfo.Sex = read.GetString(6);
                   
                    userInfo.Region = read.GetString(7);
                    userInfo.OfficeTel = read.GetString(8);
                    userInfo.ExtensionTel = read.GetString(9);
                    userInfo.Mobile = read.GetString(10);
                    userInfo.Email = read.GetString(11);
                  
                    list.Add(userInfo);
                }
                return list;
            }


        }

        #endregion

        #region 判断姓名是否存在
        /// <summary>
        /// 判断登姓名是否存在
        /// </summary>
        /// <param name="loginName"></param>
        /// <returns></returns>
        public int CheckLoginName(string loginName)
        {
            string sql = "select * from viewUserInfo where userName='" + loginName + "' ";
            int i = 0;
            SqlDataReader sdr = SQLHelper.ExecuteReader(SQLHelper.ConStr, CommandType.Text, sql, null);
            if (sdr.Read())
            {
                i = 1;
            }

            return i;

        }

        #endregion

        #region 判断原密码
        /// <summary>
        ///  判断原密码
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int CheckPassword(string loginName, string pwd)
        {
            string sqlStr = "select userId from viewUserInfo where userId='" + loginName + "' and pwd='" + pwd + "' ";
            int i = 0;
            SqlDataReader sdr = SQLHelper.ExecuteReader(SQLHelper.ConStr, CommandType.Text, sqlStr, null);
            if (sdr.Read())
            {
                i = int.Parse(sdr[0].ToString());
            }

            return i;

        }

        #endregion

        #region 修改密码
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int ChangPassword(int loginName, string pwd)
        {
            string sqlstr = "update userInfo set pwd='" + pwd + "' where userId='" + loginName + "'";
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sqlstr, null);
        }

        #endregion

        #region 修改用户信息
        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="cName"></param>
        /// <param name="pwd"></param>
        /// <param name="shopId"></param>
        /// <param name="deptId"></param>
        /// <param name="enableFlag"></param>
        /// <returns></returns>
        public int UpdateUserInfo(int userId,string userName,int deptId, int roleId, string eName, string sex,string workCity, string officeTel, string shortTel, string mobile, string email)
        {
            string sql = @"update userInfo set userName=@userName,deptId=@deptId,roleId=@roleId,englishName=@eName,sex=@sex,region=@workCity,officeTel=@officeTel,extensionTel=@shortTel,mobile=@mobile,email=@email
                           where userId=@userId";

            SqlParameter[] param = {
                                       new SqlParameter("@userId",userId),
                                       new SqlParameter("@userName",userName),
                                       
                                       new SqlParameter("@deptId",deptId),
                                       new SqlParameter("@roleId",roleId),
                                       new SqlParameter("@eName",eName),
                                       new SqlParameter("@sex",sex),
                                       new SqlParameter("@workCity",workCity),
                                       new SqlParameter("@officeTel",officeTel),
                                       new SqlParameter("@shortTel",shortTel),
                                       new SqlParameter("@mobile",mobile),
                                       new SqlParameter("@email",email),
                                    
                                   };
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sql, param);

        }
        #endregion
        
        #region 删除用户信息
        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="LoginName"></param>
        /// <returns></returns>
        public int DeleteStaff(string LoginName)
        {
            string sqlstr = "delete from userInfo where userId='" + LoginName + "'";
            return SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, sqlstr, null);


        }

        #endregion
    }
}
